<?php
/**
 * (logic) 退货报表数据回写（修正品质报表数据，以return_date时间为参数）
 */
namespace App\Logic\Quality;

use App\Models\Amazon\FbaCustomerReturn;
use App\Models\Quality\QReport;
use App\Models\Quality\QReportClassify;
use Illuminate\Support\Facades\DB;
class SyncReturnRepair
{
    public static function init()
    {
        try{

            $date = date('2021-01-01');
            $eof = date('2023-10-01');
            while(true){
                if($date == $eof)break;
                $time = strtotime($date);
                $res = QReport::query()->where('report_date',$date)->get();
                $i = 0;
                $num = 500;
                while(true){
                    if($res->isEmpty())break;
                    $_res = $res->splice($i,$num);
                    DB::beginTransaction();
                    foreach($_res as $_re){
                        $return = self::_return($time,$_re->country_id,$_re->psku_id);
                        $_re->return_num = $return->return_num ?? 0;
                        $_re->saleable_num = $return->saleable_num ?? 0;
                        $_re->save();
                    }
                    DB::commit();
                }
                sleep(2);

                $recs = QReportClassify::query()->where('report_date',$date)->get();
                while(true){
                    if($recs->isEmpty())break;
                    $_recs = $recs->splice($i,$num);
                    DB::beginTransaction();
                    foreach($_recs as $_rec){
                        $return = self::_returnC($date,$_rec->country_id,$_rec->classify_id);
                        $_rec->return_num = $return->return_num ?? 0;
                        $_rec->saleable_num = $return->saleable_num ?? 0;
                        $_rec->save();
                    }
                    DB::commit();
                }
                $date = date('Y-m-01',strtotime("midnight first day of +1 month",$time));

                echo $date.PHP_EOL;
                sleep(2);

            };
        }catch (\Exception $e){
            report($e);
        }
    }

    private static function _return($time,$countryId,$pSkuId){
        $start = date('Y-m-01 00:00:00',$time);
        $end = date('Y-m-t 23:59:59',$time);
        return FbaCustomerReturn::query()
            ->where('return_date','>=',$start)
            ->where('return_date','<=',$end)
            ->where('country_id',$countryId)
            ->where('psku_id',$pSkuId)
            ->select(DB::raw("sum(quantity) as return_num,sum(IF(detailed_disposition='SELLABLE',quantity,0)) as saleable_num"))
            ->first();
    }

    private static function _returnC($date,$countryId,$classifyId){
        return QReport::query()
            ->where('report_date',$date)
            ->where('country_id',$countryId)
            ->whereHas('sku', function ($query) use($classifyId){
                $query->where('classify_id', $classifyId);
            })
            ->select(DB::raw("sum(return_num) as return_num,sum(saleable_num) as saleable_num"))
            ->first();
    }
}
